﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.VisualBasic;

namespace Simple.Services.System.Report.Violation
{
    public class QHSEViolationReportService
    {
        private readonly SimpleDbContext _context;
        private readonly CacheService _cacheService;
        private readonly QHSEOrganizationService _organizationService;
        private readonly QHSEXyViolationTypeService _violationTypeService;
        private readonly QHSEViolationCategoryService _violationCategoryService;
        private readonly QHSEDictionaryService _dictionaryService;
        private readonly QHSEKeyWorkSpecialtyService _keyWorkSpecialtyService;
        private readonly QHSEKeyWorkTypeService _keyWorkTypeService;
        private readonly QHSEViolationHSEService _violationHSEService;
        private readonly QHSEViolationObserverService _violationObserverService;
        private static List<string> SelfCheckTypes = new List<string> { "10000009", "10000010", "10000011" };
        private static List<string> SuperviseCheckTypes = new List<string> { "10000012", "10000013", "10000014" };

        private readonly IServiceScopeFactory _serviceScopeFactory;

        public QHSEViolationReportService(SimpleDbContext context,QHSEOrganizationService organizationService, QHSEXyViolationTypeService violationTypeService, 
            QHSEViolationCategoryService violationCategoryService, QHSEDictionaryService dictionaryService, 
            QHSEKeyWorkSpecialtyService keyWorkSpecialtyService, QHSEKeyWorkTypeService keyWorkTypeService, 
            QHSEViolationHSEService violationHSEService, QHSEViolationObserverService violationObserverService
            ,CacheService cacheService, IServiceScopeFactory serviceScopeFactory)
        {
            _context = context;
            _organizationService = organizationService;
            _violationTypeService = violationTypeService;
            _violationCategoryService = violationCategoryService;
            _dictionaryService = dictionaryService;
            _keyWorkSpecialtyService = keyWorkSpecialtyService;
            _keyWorkTypeService = keyWorkTypeService;
            _violationHSEService = violationHSEService;
            _violationObserverService = violationObserverService;
            _cacheService = cacheService;
            _serviceScopeFactory = serviceScopeFactory;
        }
        private static string EscapeLikePattern(string pattern)
        {
            return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
        }

        public async Task<List<TableReport>> TableViolationTotalCheckCount(QHSETotalInputModel inputModel)
        {
            if (inputModel.TimeEnd == null || inputModel.TimeOn == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationTotalCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            List<TableReport> result = new List<TableReport>();
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();

                var query = context.Set<QHSESysXyViolation>().AsNoTracking().Where(u => u.CheckDate <= inputModel.TimeEnd && u.CheckDate >= inputModel.TimeOn).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var orgs = await context.Set<QHSESysOrganization>().AsNoTracking().Where(u => u.ParentId == inputModel.OrganizationId && u.RowState == 0).ToListAsync();
                
                TableReport table = new TableReport();
                table.OrganizationName = "总计";
                foreach (var org in orgs)
                {
                    var escapedNamePath = EscapeLikePattern(org.NamePath);
                    TableReport tableViolationReport = new TableReport();
                    tableViolationReport.OrganizationName = org.OrganizationName;
                    tableViolationReport.Value1 = await query
                        .Where(u => SelfCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();
                    tableViolationReport.Value2 = await query
                        .Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode))
                           .Join(context.Set<QHSESysOrganization>(),
                               violation => violation.OrganizationId,
                               organization => organization.OrganizationId,
                               (violation, organization) => new { Violation = violation, Organization = organization })
                           .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                           .CountAsync();
                    table.Value1 += tableViolationReport.Value1;
                    table.Value2 += tableViolationReport.Value2;
                    result.Add(tableViolationReport);
                }
                result.Add(table);
            }
            await _cacheService.SetTableViolationTotalCheckCountAsync(inputModel, result);
            return result;
        }
        public async Task<CompareReportModel> CompareViolationDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationDepartmentCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var selfViolation1 = await query.Where(u => SelfCheckTypes.Contains(u.CheckTypeCode)).CountAsync();
                var superviseViolation1 = await query.Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode)).CountAsync();

                var selfViolation2 = await comQuery.Where(u => SelfCheckTypes.Contains(u.CheckTypeCode)).CountAsync();
                var superviseViolation2 = await comQuery.Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode)).CountAsync();
                List<object> obj = new List<object>() { "违章总数对比", $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                List<object> count1 = new List<object>() { "自查数", selfViolation1, selfViolation2 };
                List<object> count2 = new List<object>() { "监督纠察数量", superviseViolation1, superviseViolation2 };
                List<object> count3 = new List<object>() { "合计", selfViolation1 + superviseViolation1, selfViolation2 + superviseViolation2 };


                result.Item.Add(obj);
                result.Item.Add(count1);
                result.Item.Add(count2);
                result.Item.Add(count3);
            }
            await _cacheService.SetCompareViolationDepartmentCheckCountAsync(inputModel, result);
            return result;
        }


        public async Task<List<TableCompareReport>> TableViolationDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationDepartmentCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }

            List<TableCompareReport> TableCompareReports = new List<TableCompareReport>();
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();

                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var orgs = await context.Set<QHSESysOrganization>().AsNoTracking().Where(u => u.ParentId == inputModel.OrganizationId && u.RowState == 0).ToListAsync();
                TableCompareReport table = new TableCompareReport();
                table.OrganizationName = "总计";
                foreach (var org in orgs)
                {
                    var escapedNamePath = EscapeLikePattern(org.NamePath);
                    TableCompareReport tableViolationReport = new TableCompareReport();
                    tableViolationReport.OrganizationName = org.OrganizationName;
                    tableViolationReport.Value1 = await query
                        .Where(u => SelfCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();
                    tableViolationReport.Value2 = await comQuery
                        .Where(u => SelfCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();
                    tableViolationReport.Total1 = tableViolationReport.Value1 + tableViolationReport.Value2;
                    tableViolationReport.Value3 = await query
                        .Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();
                    tableViolationReport.Value4 = await comQuery
                        .Where(u => SuperviseCheckTypes.Contains(u.CheckTypeCode))
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .CountAsync();
                    tableViolationReport.Total2 = tableViolationReport.Value3 + tableViolationReport.Value4;


                    if (tableViolationReport.Total2 == 0 && tableViolationReport.Total1 == 0)
                    {
                        tableViolationReport.GrowthRate = $"{0}%";
                    }
                    else if (tableViolationReport.Total2 == 0 && tableViolationReport.Total1 != 0)
                    {
                        tableViolationReport.GrowthRate = $"{-100}%";
                    }
                    else if (tableViolationReport.Total2 != 0 && tableViolationReport.Total1 == 0)
                    {
                        tableViolationReport.GrowthRate = $"{100}%";
                    }
                    else
                    {
                        tableViolationReport.GrowthRate = $"{(tableViolationReport.Total2 - tableViolationReport.Total1) / tableViolationReport.Total1 * 100}%";
                    }
                    TableCompareReports.Add(tableViolationReport);
                    table.Value1 += tableViolationReport.Value1;
                    table.Value2 += tableViolationReport.Value2;
                    table.Value3 += tableViolationReport.Value3;
                    table.Value4 += tableViolationReport.Value4;
                    table.Total1 += tableViolationReport.Total1;
                    table.Total2 += tableViolationReport.Total2;
                }
                if (table.Total2 == 0 && table.Total1 == 0)
                {
                    table.GrowthRate = $"{0}%";
                }
                else if (table.Total2 == 0 && table.Total1 != 0)
                {
                    table.GrowthRate = $"{-100}%";
                }
                else if (table.Total2 != 0 && table.Total1 == 0)
                {
                    table.GrowthRate = $"{100}%";
                }
                else
                {
                    table.GrowthRate = $"{(table.Total2 - table.Total1) / table.Total1 * 100}%";
                }
                TableCompareReports.Add(table);
            }
            await _cacheService.SetTableViolationTotalCheckCountAsync(inputModel, TableCompareReports);
            return TableCompareReports;
        }
        public async Task<CompareReportModel> CompareSelfViolationTypeCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "违章级别自查对比";
            return await CompareViolationTypeCheckCount(inputModel, SelfCheckTypes,type);
        }

        public async Task<CompareReportModel> CompareSuperviseViolationTypeCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "违章级别自查对比";
            return await CompareViolationTypeCheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<List<TableTimeReport>> TableViolationTypeCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> tableViolationTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationTypeCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var violationTypes = await _violationTypeService.GetAsync(context);
                foreach (var violationType in violationTypes)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.ViolationType.Equals(violationType.ViolationType)).CountAsync();
                    var self2 = await query1.Where(u => u.ViolationType.Equals(violationType.ViolationType)).CountAsync();
                    var su1 = await comQuery.Where(u => u.ViolationType.Equals(violationType.ViolationType)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.ViolationType.Equals(violationType.ViolationType)).CountAsync();
                    table.CompareType = violationType.ViolationType;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    tableViolationTimeReports.Add(table);
                }
            }
            await _cacheService.SetTableViolationTypeCheckCountAsync(inputModel, tableViolationTimeReports);
            return tableViolationTimeReports;
        }
        public async Task<CompareReportModel> CompareViolationTypeCheckCount(QHSEReportInputModel inputModel, List<string> strings,string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationTypeCheckCountAsync(inputModel,strings,type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var violationTypes = await _violationTypeService.GetAsync(context);
                foreach (var violationType in violationTypes)
                {
                    List<object> items = new List<object>();
                    items.Add(violationType.ViolationType);
                    var vio1 = await query.Where(u => u.ViolationType.Equals(violationType.ViolationType)).CountAsync();
                    var vio2 = await comQuery.Where(u => u.ViolationType.Equals(violationType.ViolationType)).CountAsync();
                    items.Add(vio1);
                    items.Add(vio2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareViolationTypeCheckCountAsync(inputModel, strings, type, result);
            return result;
        }

        public async Task<CompareReportModel> CompareSelfViolationCategoryCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "违章类别自查对比";
            return await CompareViolationCategoryCheckCount(inputModel, SelfCheckTypes,type);
        }

        public async Task<CompareReportModel> CompareSuperviseViolationCategoryCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "违章类别监督对比";
            return await CompareViolationCategoryCheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<List<TableTimeReport>> TableViolationCategoryCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> tableViolationTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationCategoryCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var violationCategorys = await _violationCategoryService.GetSon("不安全因素",context);
                foreach (var violationCategory in violationCategorys)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await GetViolationCountsAsync(query, violationCategory,context);
                    var self2 = await GetViolationCountsAsync(query1, violationCategory, context);
                    var su1 = await GetViolationCountsAsync(comQuery, violationCategory, context);
                    var su2 = await GetViolationCountsAsync(comQuery1, violationCategory, context);
                    table.CompareType = violationCategory;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    tableViolationTimeReports.Add(table);
                }
            }
            await _cacheService.SetTableViolationCategoryCheckCountAsync(inputModel, tableViolationTimeReports);
            return tableViolationTimeReports;
        }
        public async Task<CompareReportModel> CompareViolationCategoryCheckCount(QHSEReportInputModel inputModel, List<string> strings,string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationCategoryCheckCountAsync(inputModel,strings,type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value; 
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var violationCategorys = await _violationCategoryService.GetSon("不安全因素",context);
                foreach (var violationCategory in violationCategorys)
                {
                    List<object> items = new List<object>();
                    items.Add(violationCategory);
                    var vio1 = await GetViolationCountsAsync(query, violationCategory, context);
                    var vio2 = await GetViolationCountsAsync(comQuery, violationCategory, context);
                    items.Add(vio1);
                    items.Add(vio2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareViolationCategoryCheckCountAsync(inputModel,strings,type, result);
            return result;
        }
        private async Task<int> GetViolationCountsAsync(IQueryable<QHSESysXyViolation> query, string violationCategory,SimpleDbContext context)
        {

            var children = await _violationCategoryService.GetChildren(violationCategory,context);
            var count = await query
                .Where(v => children.Contains(v.ViolationCategory))
                .CountAsync();

            return count;
        }

        public async Task<CompareReportModel> CompareSelfViolationAgeCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "违章人员工龄自查对比";
            return await CompareViolationAgeCheckCount(inputModel, SelfCheckTypes, type);
        }

        public async Task<CompareReportModel> CompareSuperviseViolationAgeCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "违章人员工龄监督对比";
            return await CompareViolationAgeCheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<List<TableTimeReport>> TableViolationAgeCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> tableViolationTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationAgeCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var ages = await _dictionaryService.GetDictionaryAsync("1003",context);
                foreach (var age in ages)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.WorkYears.Equals(age.Item)).CountAsync();
                    var self2 = await query1.Where(u => u.WorkYears.Equals(age.Item)).CountAsync();
                    var su1 = await comQuery.Where(u => u.WorkYears.Equals(age.Item)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.WorkYears.Equals(age.Item)).CountAsync();
                    table.CompareType = age.Item;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    tableViolationTimeReports.Add(table);
                }
            }
            await _cacheService.SetTableViolationAgeCheckCountAsync(inputModel, tableViolationTimeReports);
            return tableViolationTimeReports;
        }
        public async Task<CompareReportModel> CompareViolationAgeCheckCount(QHSEReportInputModel inputModel, List<string> strings,string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationAgeCheckCountAsync(inputModel,strings,type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var ages = await _dictionaryService.GetDictionaryAsync("1003", context);
                foreach (var age in ages)
                {
                    List<object> items = new List<object>();
                    items.Add(age.Item);
                    var vio1 = await query.Where(u => u.WorkYears.Equals(age.Item)).CountAsync();
                    var vio2 = await comQuery.Where(u => u.WorkYears.Equals(age.Item)).CountAsync();
                    items.Add(vio1);
                    items.Add(vio2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareViolationAgeCheckCountAsync(inputModel, strings, type, result);
            return result;
        }

        public async Task<CompareReportModel> CompareSelfViolationDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "归属直线部门自查对比";
            return await CompareViolationDepartmentCheckCount(inputModel, SelfCheckTypes, type);
        }

        public async Task<CompareReportModel> CompareSuperviseViolationDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "归属直线部门监督对比";
            return await CompareViolationDepartmentCheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<List<TableTimeReport>> TableViolationDirectDepartmentCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> tableViolationTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationDirectDepartmentCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var departments = await _dictionaryService.GetDictionaryAsync("1008", context);
                foreach (var department in departments)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    var self2 = await query1.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    var su1 = await comQuery.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    table.CompareType = department.Item;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    tableViolationTimeReports.Add(table);
                }
            }
            await _cacheService.SetTableViolationDirectDepartmentCheckCountAsync(inputModel, tableViolationTimeReports);
            return tableViolationTimeReports;
        }
        public async Task<CompareReportModel> CompareViolationDepartmentCheckCount(QHSEReportInputModel inputModel, List<string> strings,string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationDirectDepartmentCheckCountAsync(inputModel,strings, type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var departments = await _dictionaryService.GetDictionaryAsync("1008", context);
                foreach (var department in departments)
                {
                    List<object> items = new List<object>();
                    items.Add(department.Item);
                    var vio1 = await query.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    var vio2 = await comQuery.Where(u => u.DirectDepartment.Equals(department.Item)).CountAsync();
                    items.Add(vio1);
                    items.Add(vio2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareViolationDirectDepartmentCheckCountAsync(inputModel, strings, type, result);
            return result;
        }

        public async Task<CompareReportModel> CompareSelfViolationWorkSpecialtyCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "作业专业自查对比";
            return await CompareViolationWorkSpecialtyCheckCount(inputModel, SelfCheckTypes,type);
        }

        public async Task<CompareReportModel> CompareSuperviseViolationWorkSpecialtyCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "作业专业监督对比";
            return await CompareViolationWorkSpecialtyCheckCount(inputModel, SuperviseCheckTypes, type);
        }
        public async Task<List<TableTimeReport>> TableViolationSpecialtyCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> tableViolationTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationSpecialtyCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var specialtys = await _keyWorkSpecialtyService.GetAsync(context);
                foreach (var specialty in specialtys)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    var self2 = await query1.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    var su1 = await comQuery.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    table.CompareType = specialty.Specialty;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    tableViolationTimeReports.Add(table);
                }
            }
            await _cacheService.SetTableViolationSpecialtyCheckCountAsync(inputModel, tableViolationTimeReports);
            return tableViolationTimeReports;
        }
        public async Task<CompareReportModel> CompareViolationWorkSpecialtyCheckCount(QHSEReportInputModel inputModel, List<string> strings,string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationSpecialtyCheckCountAsync(inputModel,strings,type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var specialtys = await _keyWorkSpecialtyService.GetAsync(context);
                foreach (var specialty in specialtys)
                {
                    List<object> items = new List<object>();
                    items.Add(specialty.Specialty);
                    var vio1 = await query.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    var vio2 = await comQuery.Where(u => u.WorkSpecialty.Equals(specialty.Specialty)).CountAsync();
                    items.Add(vio1);
                    items.Add(vio2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareViolationSpecialtyCheckCountAsync(inputModel,strings,type ,result);
            return result;
        }

        public async Task<CompareReportModel> CompareSelfViolationKeyWorkCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "关键作业自查对比";
            return await CompareViolationKeyWorkCheckCount(inputModel, SelfCheckTypes,type);
        }

        public async Task<CompareReportModel> CompareSuperviseViolationKeyWorkCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "关键作业监督对比";
            return await CompareViolationKeyWorkCheckCount(inputModel, SuperviseCheckTypes,type);
        }
        public async Task<List<TableTimeReport>> TableViolationWorkTypeCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> tableViolationTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationKeyWorkCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var specialtys = await _keyWorkTypeService.GetAsync(context);
                // 使用 LINQ GroupBy 去重
                var distinctSpecialtys = specialtys.GroupBy(s => s.WorkType)
                                                   .Select(g => g.First())
                                                   .ToList();
                foreach (var specialty in distinctSpecialtys)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    var self2 = await query1.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    var su1 = await comQuery.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    table.CompareType = specialty.WorkType;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    tableViolationTimeReports.Add(table);
                }
            }
            tableViolationTimeReports = tableViolationTimeReports.OrderByDescending(u => u.SelfValue1).ToList();
            await _cacheService.SetTableViolationKeyWorkCheckCountAsync(inputModel, tableViolationTimeReports);
            return tableViolationTimeReports;
        }
        public async Task<CompareReportModel> CompareViolationKeyWorkCheckCount(QHSEReportInputModel inputModel, List<string> strings,string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationKeyWorkCheckCountAsync(inputModel, strings, type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var specialtys = await _keyWorkTypeService.GetAsync(context);
                // 使用 LINQ GroupBy 去重
                var distinctSpecialtys = specialtys.GroupBy(s => s.WorkType)
                                                   .Select(g => g.First())
                                                   .ToList();
                foreach (var specialty in distinctSpecialtys)
                {
                    List<object> items = new List<object>();
                    items.Add(specialty.WorkType);
                    var vio1 = await query.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    var vio2 = await comQuery.Where(u => u.WorkType.Equals(specialty.WorkType)).CountAsync();
                    items.Add(vio1);
                    items.Add(vio2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareViolationKeyWorkCheckCountAsync(inputModel,strings,type, result);
            return result;
        }

        public async Task<CompareReportModel> CompareSelfViolationHSECheckCount(QHSEReportInputModel inputModel)
        {
            string type = "体系要素自查对比";
            return await CompareViolationHSECheckCount(inputModel, SelfCheckTypes,type);
        }

        public async Task<CompareReportModel> CompareSuperviseViolationHSECheckCount(QHSEReportInputModel inputModel)
        {
            string type = "体系要素监督对比";
            return await CompareViolationHSECheckCount(inputModel, SuperviseCheckTypes,type);
        }
        public async Task<List<TableTimeReport>> TableViolationHSECheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> tableViolationTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationHSECheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var specialtys = await _violationHSEService.GetAsync(context);
                foreach (var specialty in specialtys)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    var self2 = await query1.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    var su1 = await comQuery.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    table.CompareType = specialty.Item;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    tableViolationTimeReports.Add(table);
                }
            }
            tableViolationTimeReports = tableViolationTimeReports.OrderByDescending(u => u.SelfValue1).ToList();
            await _cacheService.SetTableViolationHSECheckCountAsync(inputModel, tableViolationTimeReports);
            return tableViolationTimeReports;
        }
        public async Task<CompareReportModel> CompareViolationHSECheckCount(QHSEReportInputModel inputModel, List<string> strings,string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationHSECheckCountAsync(inputModel, strings, type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var specialtys = await _violationHSEService.GetAsync(context);
                foreach (var specialty in specialtys)
                {
                    List<object> items = new List<object>();
                    items.Add(specialty.Item);
                    var vio1 = await query.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    var vio2 = await comQuery.Where(u => u.HseElement.Equals(specialty.Item)).CountAsync();
                    items.Add(vio1);
                    items.Add(vio2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareViolationHSECheckCountAsync(inputModel,strings,type, result);
            return result;
        }
        public async Task<CompareReportModel> CompareSelfViolationObsCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "安全观察与沟通自查对比";
            return await CompareViolationObsCheckCount(inputModel, SelfCheckTypes,type);
        }

        public async Task<CompareReportModel> CompareSuperviseViolationObsCheckCount(QHSEReportInputModel inputModel)
        {
            string type = "安全观察与沟通监督对比";
            return await CompareViolationObsCheckCount(inputModel, SuperviseCheckTypes,type);
        }
        public async Task<List<TableTimeReport>> TableViolationObsCheckCount(QHSEReportInputModel inputModel)
        {
            List<TableTimeReport> tableViolationTimeReports = new List<TableTimeReport>();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetTableViolationOBSCheckCountAsync(inputModel);
            if (cache != null)
            {
                return cache;
            }
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var query1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SelfCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery1 = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && SuperviseCheckTypes.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        query1 = query1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery1 = comQuery1
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                var specialtys = await _violationObserverService.GetObserverAsync(context);
                foreach (var specialty in specialtys)
                {
                    TableTimeReport table = new TableTimeReport();
                    var self1 = await query.Where(u => u.ObserveComm.Contains(specialty.Item1)).CountAsync();
                    var self2 = await query1.Where(u => u.ObserveComm.Contains(specialty.Item1)).CountAsync();
                    var su1 = await comQuery.Where(u => u.ObserveComm.Contains(specialty.Item1)).CountAsync();
                    var su2 = await comQuery1.Where(u => u.ObserveComm.Contains(specialty.Item1)).CountAsync();
                    table.CompareType = specialty.Item1;
                    table.SelfValue1 = self1;
                    table.SelfValue2 = self2;
                    table.SuperviseValue1 = su1;
                    table.SuperviseValue2 = su2;
                    tableViolationTimeReports.Add(table);
                }
            }
            tableViolationTimeReports = tableViolationTimeReports.OrderByDescending(u => u.SelfValue1).ToList();
            await _cacheService.SetTableViolationOBSCheckCountAsync(inputModel, tableViolationTimeReports);
            return tableViolationTimeReports;
        }
        public async Task<CompareReportModel> CompareViolationObsCheckCount(QHSEReportInputModel inputModel, List<string> strings, string type)
        {
            CompareReportModel result = new CompareReportModel();
            if (inputModel.TimeEnd1 == null || inputModel.TimeOn1 == null || inputModel.TimeEnd2 == null || inputModel.TimeOn2 == null)
            {
                return null;
            }
            var cache = await _cacheService.GetCompareViolationOBSCheckCountAsync(inputModel,strings,type);
            if (cache != null)
            {
                return cache;
            }
            var time1 = inputModel.TimeOn1.Value;
            var time2 = inputModel.TimeOn2.Value;
            using (var scope = _serviceScopeFactory.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<SimpleDbContext>();
                var query = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd1 && u.CheckDate >= inputModel.TimeOn1 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                var comQuery = context.Set<QHSESysXyViolation>().Where(u => u.CheckDate <= inputModel.TimeEnd2 && u.CheckDate >= inputModel.TimeOn2 && strings.Contains(u.CheckTypeCode)).AsQueryable();
                if (inputModel.OrganizationId != "00")
                {
                    var org = await context.Set<QHSESysOrganization>().FindAsync(inputModel.OrganizationId);
                    if (org != null)
                    {
                        var escapedNamePath = EscapeLikePattern(org.NamePath);
                        query = query
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                        comQuery = comQuery
                            .Join(context.Set<QHSESysOrganization>(),
                                violation => violation.OrganizationId,
                                organization => organization.OrganizationId,
                                (violation, organization) => new { Violation = violation, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                    }
                }
                List<object> obj = new List<object>() { type, $"{time1.Year}年{time1.Month}月", $"{time2.Year}年{time2.Month}月" };

                result.Item.Add(obj);
                var specialtys = await _violationObserverService.GetObserverAsync(context);
                foreach (var specialty in specialtys)
                {
                    List<object> items = new List<object>();
                    items.Add(specialty.Item1);
                    var vio1 = await query.Where(u => u.ObserveComm.Contains(specialty.Item1)).CountAsync();
                    var vio2 = await comQuery.Where(u => u.ObserveComm.Contains(specialty.Item1)).CountAsync();
                    items.Add(vio1);
                    items.Add(vio2);
                    result.Item.Add(items);
                }
            }
            await _cacheService.SetCompareViolationOBSCheckCountAsync(inputModel,strings,type, result);
            return result;
        }
    }
}
